import 'dart:async';
import 'dart:io';

import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

import '../models/app_settings.dart';
import '../models/emotion_record.dart';

/// SQLite数据库服务
/// SQLite database service
class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();

  /// 获取单例实例
  /// Get singleton instance
  static DatabaseService get instance => _instance;

  static Database? _database;

  /// 数据库版本
  /// Database version
  static const int _databaseVersion = 2;

  /// 数据库名称
  /// Database name
  static const String _databaseName = 'emotion_healing.db';

  /// 获取数据库实例
  /// Get database instance
  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  /// 初始化数据库（公开方法）
  /// Initialize database (public method)
  Future<void> initDatabase() async {
    _database ??= await _initDatabase();
  }

  /// 重置数据库（删除并重新创建）
  /// Reset database (delete and recreate)
  Future<void> resetDatabase() async {
    await close();
    await deleteDatabase();
    _database = await _initDatabase();
  }

  /// 初始化数据库
  /// Initialize database
  Future<Database> _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);

    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建数据库表
  /// Create database tables
  Future<void> _onCreate(Database db, int version) async {
    // 创建情绪记录表
    // Create emotion record table
    await db.execute('''
      CREATE TABLE emotion_records (
        id TEXT PRIMARY KEY,
        emotion_type TEXT NOT NULL,
        emotion_intensity INTEGER NOT NULL,
        diary_content TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        audio_path TEXT,
        image_paths TEXT,
        tags TEXT
      )
    ''');

    // 创建媒体文件表
    // Create media file table
    await db.execute('''
      CREATE TABLE media_files (
        id TEXT PRIMARY KEY,
        record_id TEXT NOT NULL,
        file_type TEXT NOT NULL,
        file_path TEXT NOT NULL,
        file_size INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        FOREIGN KEY (record_id) REFERENCES emotion_records (id) ON DELETE CASCADE
      )
    ''');

    // 创建应用设置表
    // Create app settings table
    await db.execute('''
      CREATE TABLE app_settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    ''');

    // 创建索引以提高查询性能
    // Create indexes for better query performance
    await db.execute(
        'CREATE INDEX idx_emotion_records_created_at ON emotion_records(created_at)');
    await db.execute(
        'CREATE INDEX idx_emotion_records_emotion_type ON emotion_records(emotion_type)');
    await db.execute(
        'CREATE INDEX idx_media_files_record_id ON media_files(record_id)');

    // 插入默认设置
    // Insert default settings
    await _insertDefaultSettings(db);

    // 插入示例数据
    // Insert sample data
    await _insertSampleData(db);
  }

  /// 数据库升级
  /// Database upgrade
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // 处理数据库版本升级
    // Handle database version upgrade
    if (oldVersion < 2) {
      // 从版本1升级到版本2：修复表结构
      // Upgrade from version 1 to 2: fix table structure
      await _upgradeToVersion2(db);
    }
  }

  /// 升级到版本2
  /// Upgrade to version 2
  Future<void> _upgradeToVersion2(Database db) async {
    // 删除旧表并重新创建
    // Drop old table and recreate
    await db.execute('DROP TABLE IF EXISTS emotion_records');

    // 重新创建情绪记录表
    // Recreate emotion record table
    await db.execute('''
      CREATE TABLE emotion_records (
        id TEXT PRIMARY KEY,
        emotion_type TEXT NOT NULL,
        emotion_intensity INTEGER NOT NULL,
        diary_content TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        audio_path TEXT,
        image_paths TEXT,
        tags TEXT
      )
    ''');

    // 重新创建索引
    // Recreate indexes
    await db.execute(
        'CREATE INDEX idx_emotion_records_created_at ON emotion_records(created_at)');
    await db.execute(
        'CREATE INDEX idx_emotion_records_emotion_type ON emotion_records(emotion_type)');

    // 重新插入示例数据
    // Reinsert sample data
    await _insertSampleData(db);
  }

  /// 插入默认设置
  /// Insert default settings
  Future<void> _insertDefaultSettings(Database db) async {
    final defaultSettings = [
      AppSettings(
        key: SettingsKeys.themeMode,
        value: AppThemeMode.system.name,
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.notificationEnabled,
        value: 'true',
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.notificationTime,
        value: '20:00',
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.fontSize,
        value: FontSize.medium.name,
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.firstLaunch,
        value: 'true',
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.language,
        value: 'zh_CN',
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.autoBackup,
        value: 'false',
        updatedAt: DateTime.now(),
      ),
      AppSettings(
        key: SettingsKeys.reminderFrequency,
        value: ReminderFrequency.daily.name,
        updatedAt: DateTime.now(),
      ),
    ];

    for (final setting in defaultSettings) {
      await db.insert('app_settings', setting.toJson());
    }
  }

  /// 插入示例数据
  /// Insert sample data
  Future<void> _insertSampleData(Database db) async {
    final now = DateTime.now();

    // 创建过去7天的示例情绪记录
    // Create sample emotion records for the past 7 days
    final sampleRecords = [
      EmotionRecord(
        id: 'sample_1',
        emotionType: EmotionType.happy,
        emotionIntensity: 8,
        diaryContent: '今天心情很好！完成了很多工作，感觉很有成就感。晚上和朋友一起吃饭，聊得很开心。',
        createdAt: now.subtract(const Duration(days: 6)),
        updatedAt: now.subtract(const Duration(days: 6)),
      ),
      EmotionRecord(
        id: 'sample_2',
        emotionType: EmotionType.calm,
        emotionIntensity: 7,
        diaryContent: '平静的一天，在公园里散步，看到了美丽的夕阳。内心感到很宁静。',
        createdAt: now.subtract(const Duration(days: 5)),
        updatedAt: now.subtract(const Duration(days: 5)),
      ),
      EmotionRecord(
        id: 'sample_3',
        emotionType: EmotionType.anxious,
        emotionIntensity: 6,
        diaryContent: '明天有重要的会议，有点紧张。不过已经准备得差不多了，应该没问题。',
        createdAt: now.subtract(const Duration(days: 4)),
        updatedAt: now.subtract(const Duration(days: 4)),
      ),
      EmotionRecord(
        id: 'sample_4',
        emotionType: EmotionType.excited,
        emotionIntensity: 9,
        diaryContent: '收到了心仪工作的offer！太开心了！所有的努力都值得了。',
        createdAt: now.subtract(const Duration(days: 3)),
        updatedAt: now.subtract(const Duration(days: 3)),
      ),
      EmotionRecord(
        id: 'sample_5',
        emotionType: EmotionType.sad,
        emotionIntensity: 4,
        diaryContent: '今天有点低落，可能是天气的原因。希望明天会更好。',
        createdAt: now.subtract(const Duration(days: 2)),
        updatedAt: now.subtract(const Duration(days: 2)),
      ),
      EmotionRecord(
        id: 'sample_6',
        emotionType: EmotionType.grateful,
        emotionIntensity: 8,
        diaryContent: '感谢身边所有支持我的人。今天收到了朋友的暖心消息，很感动。',
        createdAt: now.subtract(const Duration(days: 1)),
        updatedAt: now.subtract(const Duration(days: 1)),
      ),
      EmotionRecord(
        id: 'sample_7',
        emotionType: EmotionType.content,
        emotionIntensity: 7,
        diaryContent: '今天是美好的一天，做了喜欢的事情，见了想见的人。生活就是这样简单而美好。',
        createdAt: now,
        updatedAt: now,
      ),
    ];

    for (final record in sampleRecords) {
      await db.insert('emotion_records', record.toJson());
    }
  }

  /// 关闭数据库
  /// Close database
  Future<void> close() async {
    final db = await database;
    await db.close();
    _database = null;
  }

  /// 删除数据库
  /// Delete database
  Future<void> deleteDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);

    if (await File(path).exists()) {
      await File(path).delete();
    }

    _database = null;
  }

  /// 获取数据库路径
  /// Get database path
  Future<String> getDatabasePath() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    return join(documentsDirectory.path, _databaseName);
  }

  /// 检查数据库是否存在
  /// Check if database exists
  Future<bool> databaseExists() async {
    final path = await getDatabasePath();
    return await File(path).exists();
  }

  /// 获取数据库大小
  /// Get database size
  Future<int> getDatabaseSize() async {
    final path = await getDatabasePath();
    if (await File(path).exists()) {
      return await File(path).length();
    }
    return 0;
  }

  /// 备份数据库
  /// Backup database
  Future<String> backupDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final sourcePath = join(documentsDirectory.path, _databaseName);
    final backupPath = join(
      documentsDirectory.path,
      'backup_${DateTime.now().millisecondsSinceEpoch}_$_databaseName',
    );

    if (await File(sourcePath).exists()) {
      await File(sourcePath).copy(backupPath);
      return backupPath;
    }

    throw Exception('Database file not found');
  }

  /// 恢复数据库
  /// Restore database
  Future<void> restoreDatabase(String backupPath) async {
    if (!await File(backupPath).exists()) {
      throw Exception('Backup file not found');
    }

    await close();

    final documentsDirectory = await getApplicationDocumentsDirectory();
    final targetPath = join(documentsDirectory.path, _databaseName);

    await File(backupPath).copy(targetPath);
  }
}
